﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlServerCe;
using System.Linq;
using System.Text;
using PolePositionMobileUtilityGeneral;
using System.Threading;

namespace PolePositionMobileUtilityData
{
    public class DInspectionResult
    {
        public static DataRow GetById(int inspectionId)
        {
            DataTable _Dt = new DataTable();
            SqlCeConnection _Con = null;
            SqlCeDataAdapter _Da = null;
            StringBuilder _sql = new StringBuilder();
            try
            {
                _Con = Utils.GetConnection();
                #region Query
                _sql.AppendLine("SELECT ");
                _sql.AppendLine("   U.UtilityInspectionResultId, ");
                _sql.AppendLine("   U.UtilityInspectionFk, ");
                _sql.AppendLine("   U.Comments, ");
                _sql.AppendLine("   U.LastInspection, ");
                _sql.AppendLine("   U.InspectionTypeFk, ");
                _sql.AppendLine("   I.Name as InspectionType_Name, ");
                _sql.AppendLine("   U.ResultTypeFk, ");
                _sql.AppendLine("   U.isElectrical, ");
                _sql.AppendLine("   R.Name as ResultType_Name ");
                _sql.AppendLine(" FROM tb_UtilityInspectionResults U ");
                _sql.AppendLine("   LEFT JOIN tb_InspectionTypes I ON U.InspectionTypeFk = I.InspectionTypeId");
                _sql.AppendLine("   LEFT JOIN tb_ResultTypes R ON U.ResultTypeFk= R.ResultTypeId ");
                _sql.AppendLine(" Where U.UtilityInspectionResultId = @UtilityInspectionResultId");
                #endregion Query
                _Da = new SqlCeDataAdapter(_sql.ToString(), _Con);
                _Da.SelectCommand.CommandType = CommandType.Text;
                _Da.SelectCommand.Parameters.Add(Utils.CreateIntParameter("@UtilityInspectionResultId", ParameterDirection.Input, false, inspectionId));
                _Da.Fill(_Dt);
                if (_Dt.Rows.Count > 0)
                    return _Dt.Rows[0];
                else
                    return null;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (_Con != null)
                {
                    _Con.Close();
                }
            }
        }

        public static int Insert(int utilityInspectionFk, int inspectionTypeFk, int resultTypeFk, string lastInspection, string comments, bool isElectrical, SqlCeTransaction tr)
        {
            DataTable _Dt = new DataTable();
            SqlCeDataAdapter _Da = null;
            StringBuilder _sql = new StringBuilder();
            try
            {
                #region Query
                _sql.AppendLine("Insert into tb_UtilityInspectionResults (UtilityInspectionFk,InspectionTypeFk,ResultTypeFk,LastInspection,Comments, IsElectrical) values( ");
                _sql.AppendLine("   @UtilityInspectionFk, ");
                _sql.AppendLine("   @InspectionTypeFk, ");
                _sql.AppendLine("   @ResultTypeFk, ");
                _sql.AppendLine("   @LastInspection, ");
                _sql.AppendLine("   @Comments,");
                _sql.AppendLine("   @isElectrical )");

                #endregion Query
                _Da = new SqlCeDataAdapter(_sql.ToString(), (SqlCeConnection)tr.Connection);

                #region Values

                _Da.SelectCommand.Parameters.Add(Utils.CreateIntParameter("@UtilityInspectionFk", ParameterDirection.Input, false, utilityInspectionFk));
                _Da.SelectCommand.Parameters.Add(Utils.CreateIntParameter("@InspectionTypeFk", ParameterDirection.Input, false,  inspectionTypeFk ));
                _Da.SelectCommand.Parameters.Add(Utils.CreateIntParameter("@ResultTypeFk", ParameterDirection.Input, false, resultTypeFk));
                _Da.SelectCommand.Parameters.Add(Utils.CreateNVarCharParameter("@LastInspection", 2, ParameterDirection.Input, false, !string.IsNullOrEmpty(lastInspection) ? (object)lastInspection : (object)DBNull.Value));
                _Da.SelectCommand.Parameters.Add(Utils.CreateNVarCharParameter("@Comments", 512, ParameterDirection.Input, false, !string.IsNullOrEmpty(comments) ? (object)comments : (object)DBNull.Value));
                _Da.SelectCommand.Parameters.Add(Utils.CreateBitParameter("@isElectrical", ParameterDirection.Input, false, isElectrical));



                #endregion


                _Da.SelectCommand.CommandType = CommandType.Text;
                _Da.SelectCommand.ExecuteNonQuery();

                _sql = new StringBuilder();
                _sql.AppendLine("Select max(UtilityInspectionResultId) from tb_UtilityInspectionResults");

                _Da = new SqlCeDataAdapter(_sql.ToString(), (SqlCeConnection)tr.Connection);
                _Da.SelectCommand.CommandType = CommandType.Text;
                return (Int32)_Da.SelectCommand.ExecuteScalar();

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static void Update(int inspectionResultId, int utilityInspectionFk, int inspectionTypeFk, int resultTypeFk, string lastInspection, string comments, bool isElectrical, SqlCeTransaction tr)
        {
            DataTable _Dt = new DataTable();
            SqlCeDataAdapter _Da = null;
            StringBuilder _sql = new StringBuilder();
            try
            {
                #region Query
                _sql.AppendLine("Update tb_UtilityInspectionResults ");
                _sql.AppendLine(" SET  UtilityInspectionFk =  @UtilityInspectionFk, ");
                _sql.AppendLine("      InspectionTypeFk =  @InspectionTypeFk, ");
                _sql.AppendLine("      ResultTypeFk=@ResultTypeFk, ");
                _sql.AppendLine("      LastInspection = @LastInspection, ");
                _sql.AppendLine("      Comments = @Comments, ");
                _sql.AppendLine("      isElectrical = @isElectrical ");
                _sql.AppendLine("WHERE UtilityInspectionResultId = @UtilityInspectionResultId");

                #endregion Query
                _Da = new SqlCeDataAdapter(_sql.ToString(), (SqlCeConnection)tr.Connection);

                #region Values

                _Da.SelectCommand.Parameters.Add(Utils.CreateIntParameter("@UtilityInspectionResultId", ParameterDirection.Input, false, inspectionResultId));
                _Da.SelectCommand.Parameters.Add(Utils.CreateIntParameter("@UtilityInspectionFk", ParameterDirection.Input, false, utilityInspectionFk));
                _Da.SelectCommand.Parameters.Add(Utils.CreateIntParameter("@InspectionTypeFk", ParameterDirection.Input, false, inspectionTypeFk));
                _Da.SelectCommand.Parameters.Add(Utils.CreateIntParameter("@ResultTypeFk", ParameterDirection.Input, false, resultTypeFk));
                _Da.SelectCommand.Parameters.Add(Utils.CreateNVarCharParameter("@LastInspection", 2, ParameterDirection.Input, false, !string.IsNullOrEmpty(lastInspection) ? (object)lastInspection : (object)DBNull.Value));
                _Da.SelectCommand.Parameters.Add(Utils.CreateNVarCharParameter("@Comments", 512, ParameterDirection.Input, false, !string.IsNullOrEmpty(comments) ? (object)comments : (object)DBNull.Value));
                _Da.SelectCommand.Parameters.Add(Utils.CreateNVarCharParameter("@isElectrical", 255, ParameterDirection.Input, false, isElectrical));



                #endregion


                _Da.SelectCommand.CommandType = CommandType.Text;
                _Da.SelectCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static void Delete(int inspectionResultId, SqlCeTransaction tr)
        {
            StringBuilder _Sql = new StringBuilder(); ;
            SqlCeCommand _Cmd;
            #region Query
            _Sql.AppendLine("Delete tb_UtilityInspectionResults where UtilityInspectionResultId = @UtilityInspectionResultId");
            #endregion Query
            _Cmd = new SqlCeCommand(_Sql.ToString(), (SqlCeConnection)tr.Connection);
            _Cmd.Transaction = tr;
            #region Parameters
            _Cmd.Parameters.Add(Utils.CreateIntParameter("@UtilityInspectionResultId", ParameterDirection.Input, false, inspectionResultId));
            #endregion Parameters
            _Cmd.ExecuteNonQuery();
        }

        public static void ClearDatabase()
        {
            SqlCeConnection _Con = null;
            SqlCeDataAdapter _Da = null;
            StringBuilder _Sql = new StringBuilder(); ;
            try
            {
                _Con = Utils.GetConnection();
                _Sql = new StringBuilder("Delete tb_Images");
                _Da = new SqlCeDataAdapter(_Sql.ToString(), _Con);
                _Da.SelectCommand.ExecuteNonQuery();
                _Sql = new StringBuilder("Delete tb_UtilityInspections");
                _Da = new SqlCeDataAdapter(_Sql.ToString(), _Con);
                _Da.SelectCommand.ExecuteNonQuery();
                _Sql = new StringBuilder("Delete tb_UtilityInspectionResultsXNotes");
                _Da = new SqlCeDataAdapter(_Sql.ToString(), _Con);
                _Da.SelectCommand.ExecuteNonQuery();
                _Sql = new StringBuilder("Delete tb_UtilityInspectionResults");
                _Da = new SqlCeDataAdapter(_Sql.ToString(), _Con);
                _Da.SelectCommand.ExecuteNonQuery();



            }
            catch (Exception _Ex)
            {
                throw _Ex;
            }
            finally
            {
                if (_Con != null)
                {
                    _Con.Close();
                }
            }
            
        }

    }
}
